let mongoose = require('mongoose')
const {Schema} = require("mongoose");
// const {allCityData} = require("../data_mock/county_data");
// const {cityModel} = require("./city_sales");
// const {countyModel} = require("./county_sales");

mongoose.set('strictQuery', true);
mongoose.connect("mongodb://127.0.0.1/epi_thesis")

let provinceSchema = new Schema({
    city:{//省名
        type:String,
        required: true
    },
    province_code:{//自身的code
        type:Number,
        required: true
    },
    year:{//销售年份
        type:String,
        required: true
    },
    platform_classification:{//平台分类销售数据
        type:Schema.Types.Mixed,
        required: true
    },
    sales:{//龙虾分类销售数据
        type:Schema.Types.Mixed,
        required:true
    },
    level:{//城市级别
        type:Number,
        required:true
    }
})

//定义模型
let provinceModel = mongoose.model("province_year",provinceSchema,"province_year")

//操作数据库的方法（在router_handler中调用）
//初始展示的数据
const initShowData = async (province) => {
    const getInitCode = (province)=>{
        return provinceModel.find({city: province},{province_code: 1})
            .then(res=> {
                return res
            })
            .catch(err=>{
                return false
            })
    }
    const data = await getInitCode(province)
    let code = data[0].province_code

    return provinceModel.aggregate([
        {
            $lookup:{
                from:'city_year',
                localField:'province_code',
                foreignField:'province_code',
                as:'city_info'
            }
        },
        {
            $match:{
                'province_code':code
            }
        }
    ])
        .then((res)=>{
            for (let i = 0; i < res.length; i++) {
                let counties = res[i].city_info.filter(item=>{
                    return item.year === res[i].year
                })
                res[i].city_info.splice(0,res[i].city_info.length,counties)
            }
            return JSON.parse(JSON.stringify(res)).sort((a,b)=>{
                return a.year.slice(0,4) - b.year.slice(0,4)
            })
        })
        .catch((err)=>{
            console.log(err)
            return false
        })
}

//查询有哪些年
const getYear = () => {
    return provinceModel.distinct('year')
        .then(res=>res)
        .catch(err=>{
            console.log(err)
            return false
        })
}

const allProvince = async () => {
    let time = await getYear()
    let allData = []
    for (let i = 0; i < time.length; i++) {
        let getData = () => {
            return provinceModel.find({year: time[i]})
                .then(res => res)
                .catch(err => {
                    console.log(err)
                    return false
                })
        }
        allData.push(await getData())
    }
    return allData
}

//雷达图数据
const radarData = async () => {
    let provinceData = await allProvince()

    let radarAllData = []
    let radarName = []
    if (provinceData.length !== 0){
        //获取雷达图角上的名称
        provinceData[0][0].platform_classification.platform_sales.map(item => {
            radarName.push({text:Object.keys(item)[0],max:1,min:0})
        })

        provinceData.map(item => {
            let platformSum = [] //每个省份的每年每个平台的总销量
            let platformTotalSales = []  //各平台分类的总销量
            let radarPlatformName = []
            item.map(info => {
                let salesArray = []
                let platformSales = []  //平台分类的数据（绿尾（京东，淘宝，拼多多，盒马，美团），其他，红尾）
                let products = info.platform_classification.platform_sales
                let keys = ''
                products.map(sales => {
                    let sum = 0
                    let values = Object.values(...Object.values(sales))
                    keys = Object.keys(...Object.values(sales))
                    values.map(value => {
                        sum += +value
                    })
                    platformSales.push(values)
                    salesArray.push(sum)
                })
                radarPlatformName = keys
                platformSum.push(salesArray)
                platformTotalSales.push(platformSales)
            })

            //判断分类名并命名别名
            for (let i = 0; i < radarPlatformName.length; i++) {
                if (radarPlatformName[i] === 'red_shell_sales'){
                    radarPlatformName.splice(i,1,'红尾虾')
                }else if (radarPlatformName[i] === 'green_shell_sales'){
                    radarPlatformName.splice(i,1,'绿尾虾')
                }else {
                    radarPlatformName.splice(i,1,'其他')
                }
            }

            //各个平台的总销量
            let jdSales = 0
            let tbSales = 0
            let pddSales = 0
            let hmSales = 0
            let mtSales = 0
            platformSum.map(item => {
                jdSales += item[0]
                tbSales += item[1]
                pddSales += item[2]
                hmSales += item[3]
                mtSales += item[4]
            })

            let greenPlatformSales = [0,0,0,0,0]
            let otherPlatformSales = [0,0,0,0,0]
            let redPlatformSales = [0,0,0,0,0]
            //计算分类求和方法
            let getPlatformSales = (platform,item,i) => {
                platform.push(
                    platform[0] + item[0][i],
                    platform[1] + item[0][i],
                    platform[2] + item[0][i],
                    platform[3] + item[0][i],
                    platform[4] + item[0][i]
                )
                platform.splice(0,platform.length/2)
            }

            platformTotalSales.map(item => {
                for (let i = 0; i < item[0].length; i++) {
                    switch (i){
                        case 0:
                            getPlatformSales(redPlatformSales,item,i)
                            break;
                        case 1:
                            getPlatformSales(greenPlatformSales,item,i)
                            break;
                        case 2:
                            getPlatformSales(otherPlatformSales,item,i)
                            break;
                        default:
                            break
                    }
                }
            })

            let productTotalSales = [jdSales,tbSales,pddSales,hmSales,mtSales] //每年每个平台的总销量

            //计算占比
            let redPercentageSales = []
            let greenPercentageSales = []
            let otherPercentageSales = []

            let percentageFunc = (data,percentageArray,item) => {
                let percentageValue = 0
                data.map(percentage => {
                    percentageValue = Math.round(percentage/item * 100) / 100
                })
                percentageArray.push(percentageValue)
            }
            productTotalSales.map(item => {
                percentageFunc(redPlatformSales,redPercentageSales,item)
                percentageFunc(greenPlatformSales,greenPercentageSales,item)
                percentageFunc(otherPlatformSales,otherPercentageSales,item)
            })
            radarAllData.push(
                {
                    year:item[0].year,
                    radarName,
                    productTotalSales,
                    radarPlatformName,
                    redPercentageSales,
                    greenPercentageSales,
                    otherPercentageSales
                })
        })
    }
    return radarAllData
}

//品牌分类图表数据(省)
const platformClassificationData = async () => {
    let timeData = await getYear()
    let platformAllData = []

    let getData = (time) => {
        return provinceModel.aggregate([
            {
                $match:{
                    // $and:[{year:`${time}`}]
                    year:time
                }
            },
            {
                $project:{
                    city:1,
                    year:1,
                    // sales:1,
                    redPlatform:'$sales.sales_red_sum',
                    greenPlatform:'$sales.sales_green_sum',
                    otherPlatform:'$sales.sales_other_sum',
                }
            }
        ])
            .then(res => {
                let city = []
                let redPlatform = []
                let greenPlatform = []
                let otherPlatform = []
                let platformSum = []
                for (let i = 0; i < res.length; i++) {
                    city.push(res[i].city)
                    redPlatform.push(res[i].redPlatform)
                    greenPlatform.push(res[i].greenPlatform)
                    otherPlatform.push(res[i].otherPlatform)
                    let  sum = res[i].redPlatform + res[i].greenPlatform + res[i].otherPlatform
                    platformSum.push(sum)
                }
                let legendName = ["销售总量","红尾虾","绿尾虾","其他"]
                platformAllData.push({year:res[0].year,
                    city,
                    allData:[
                        platformSum,
                        redPlatform,
                        greenPlatform,
                        otherPlatform
                    ],
                    legendName
                })
                return platformAllData
            })
            .catch(err => {
                console.log(err)
                return false
            })
    }

    for (let i = 0; i < timeData.length; i++) {
        await getData(timeData[i])
    }
    return JSON.parse(JSON.stringify(platformAllData)).sort((a,b) => {
        return a.year - b.year
    })
}

//平台分类图表数据(省)
const productClassificationData = async () => {
    let timeData = await getYear()
    let productAllData = []

    let getData = (time) => {
        return provinceModel.aggregate([
            {
                $match:{
                    year:time
                }
            },
            {
                $project:{
                    city:1,
                    year:1,
                    platform_classification:1,
                }
            }
        ])
            .then(res => {
                let city = []
                let jdProduct = []
                let tbProduct = []
                let pddProduct = []
                let hmProduct = []
                let mtProduct = []
                res.map(item => {
                    city.push(item.city)
                    let allProductSales = []
                    let sales = item.platform_classification.platform_sales
                    for (let i = 0; i < sales.length; i++) {
                        let values = Object.values(...Object.values(sales[i]))
                        let sum = 0
                        values.map(value => {
                            sum += +value
                        })
                        allProductSales.push(sum)
                    }
                    jdProduct.push(allProductSales[0])
                    tbProduct.push(allProductSales[1])
                    pddProduct.push(allProductSales[2])
                    hmProduct.push(allProductSales[3])
                    mtProduct.push(allProductSales[4])
                })
                let legendName = ['京东','淘宝','拼多多','盒马','美团']
                productAllData.push({
                    city,
                    year:time,
                    allData:[
                        jdProduct,
                        tbProduct,
                        pddProduct,
                        hmProduct,
                        mtProduct
                    ],
                    legendName
                })
                return productAllData
            })
            .catch(err => {
                console.log(err)
                return false
            })
    }

    for (let i = 0; i < timeData.length; i++) {
        await getData(timeData[i])
    }

    return JSON.parse(JSON.stringify(productAllData)).sort((a,b) => {
        return a.year - b.year
    })
}

const getRanking = async () => {
    let timeData = await getYear()
    let rankingAllData = []

    const getInfo = (time) => {
        return provinceModel.aggregate([
            {
                $match:{
                    year:time
                }
            },
            {
                $project:{
                    city:1,
                    year:1,
                    redPlatform:'$sales.sales_red_sum',
                    greenPlatform:'$sales.sales_green_sum',
                    otherPlatform:'$sales.sales_other_sum',
                }
            }
        ])
            .then((res)=>{
                let salesInfo = []
                for (let i = 0; i < res.length; i++) {
                    let sum = res[i].redPlatform + res[i].greenPlatform + res[i].otherPlatform
                    salesInfo.push({city:res[i].city,sales:sum})
                }

                rankingAllData.push({year:res[0].year,
                    salesInfo:JSON.parse(JSON.stringify(salesInfo)).sort((a,b) => {
                        return b.sales - a.sales
                    })})
                return rankingAllData
            })
            .catch((err)=>{
                console.log(err)
                return false
            })
    }

    for (let i = 0; i < timeData.length; i++) {
        await getInfo(timeData[i])
    }

    return JSON.parse(JSON.stringify(rankingAllData)).sort((a,b) => {
        return a.year - b.year
    })
}

module.exports = {provinceModel,
    initShowData,
    getYear,
    allProvince,
    radarData,
    platformClassificationData,
    getRanking,
    productClassificationData
}
